Database systems, methods and computer program products are widely used for information management. More specifically, database systems, methods and computer program products may be used to reliably manage a large amount of data in a multi-user environment, so that many users can concurrently access the same data. Database systems, methods and computer program products generally include a database that actually stores the data, a database management system and one or more applications that interface with the database management system to provide, for example, user interfaces and other applications.
One widely available database system is the Oracle8i database system that is marketed by Oracle Corporation and that is described, for example, in publications entitled Oracle8i Concepts, Release 8.1.5, February 1999, Part No. A67781-01, 1999, and Oracle8i Administrator's Guide, Release 8.1.5, February 1999, Part A67772-01, 1999. The disclosures of both of these publications are hereby incorporated herein by reference in their entirety. The design and operation of database systems, methods and computer program products are well known to those having skill in the art, and need not be described further herein.
At a high level, a relational database may be considered as consisting of a set of tables. Each table typically includes a set of columns (fields). Each table also typically includes a “primary key.” A primary key is a column which uniquely identifies each record in a table or a set of its columns whose combined values uniquely identify each record in that table. To speed up query searching, conventionally an index is built on the primary key of each table. An index that is based on a primary key column or columns is often called a primary key index, or primary index for short.
If a table includes a set of non-primary-key columns, i.e. a column or columns that by themselves collectively do not uniquely identify records in that table, whose combined values are used to identify records in other tables by matching the primary key values in the other tables, these columns are typically referred to as a foreign key. Often indices are built on the foreign key columns of a table, and these indices are referred to as foreign key indices.
A table having a foreign key may be considered a “child table” of the table or tables to which the foreign key points. A table which is pointed to by a foreign key or keys from another table may be considered a “parent table” of the other tables. The validity of a record in a child table with a foreign key or keys typically depends on the existence of records in all of its parent tables to which foreign key values of a record point. The referential integrity constraints between parent and child tables are enforced by a database management system that typically attempts to enforce the constraint that the foreign key of each child record must point to a parent record.
In relational database design, real world entities are normally modeled by entity tables, i.e., each of these entity table models, or represents, a class of real world entities. Between two or more entity tables the relationship is often one-to-many, meaning that each record in one of the two or more entity tables can relate to more than one record in the other entity table or tables, or many-to-many, meaning that each record in each of the two or more entity tables can relate to more than one record in the other entity table or tables.
Typically, the maximum granularity of the number of associations for each given record is not fixed in advance. There are many ways to represent a many-to-many relationship between two entity tables. One conventional way is to define another table, called a “relationship table,” whose primary key includes both the primary keys of the two entity tables it relates. One such example is illustrated in FIG. 1, where Table A and Table B are entity tables with primary keys a and b, respectively, and Table C is a relationship table. Table C has a primary key which includes the primary keys a and b of Tables A and B and has a primary index based on those primary keys. Also Table C has foreign keys a and b and, therefore, is considered a child table of both Table A and Table B such that each of the foreign keys of each record in Table C point to a record in Table A and a record in Table B respectively.
In database design, it may be desirable to create multiple relationships. A multiple relationship is one where entries in a given table may have a one-to-many relationship to entries in several other tables. Such a relationship can be designed using conventional database schema design methos, as illustrated by the entity relationship diagrams (ERDs) in FIGS. 2A and 2B. The ERDs in FIG. 2A illustrate a multiple one-to-many relationship and in FIG. 2B illustrate a multiple many-to-many. As seen in FIG. 2A, entity tables A, B1, B2, B3, . . . , Bm have primary keys (PKs) a, b1, b2, b3, . . . , bm, respectively, where a, b1, b2, b3, . . . , bm, are of the same data type. The multiple one-to-many relationship between table A and tables B1, B2, B3, . . . , Bm may be expressed if each given record in A may relate to one or more records in one or more of tables B1, B2, B3, . . . , Bm. Such is illustrated in FIG. 2A by the foreign keys (FK) of Table A each pointing to the tables B1, B2, B3, . . . , Bm. FIG. 2B illustrates a conventional design that may be used to express the multiple many-to-many relationship where table A and tables B1, B2, B3, . . . , Bm, are related by relationship tables R1, R2, R3, . . . , Rm.
As can be seen from the ERDs in FIGS. 2A and 2B, each record in Table A can be associate with records in more than one second tables. Thus, in conventional database design to express multiple relationships, it is possible to have a record in Table A relating to more than one record in more than one table from tables B1, B2, B3, . . . , Bm. In the conventional technique illustrated in FIG. 2B, there are m relationship tables created, which may increase the complexity of the database schema. As a result, extra development effort may be required. Furthermore, the increased complexity may increase the difficulty in maintaining the database. Finally, should there be changing requirements to also be able to relate records in A to records in another existing table, for example, Table Bm+1, a new relationship Table Rm+1, may need to be created to represent the potential associations between records in Table A and Table Bm+1. Such a change may require a database schema change. Schema changes may be expensive, in terms of unnecessary development cost and/or service operation interruption.
However, in database design, it may also be desirable to create multiple but exclusive relationships. A multiple but exclusive relationship is one where entries in one table can relate to multiple records from exactly one of several second tables. Such record associations are illustrated in FIG. 2C for one-to-many and FIG. 2D for many-to-many multiple but exclusive relationships, where the number of second tables m is 2.